package dao;

import beans.Salario;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class SalarioDAO {
    
    PreparedStatement pst;
    ResultSet rs;
    
    public SalarioDAO() {
    }
    
    public Salario load(int cpf, int mes, int ano) {
        Salario sal = new Salario();

        String query = "SELECT * FROM salario WHERE cpf = AND mes = ? AND ano = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, cpf);
            pst.setInt(2, mes);
            pst.setInt(3, ano);

            rs = pst.executeQuery();

            if (rs.next()) {
                sal.setIdDespesa(rs.getInt("idDespesa"));
                sal.setCpf(rs.getInt("cpf"));
                sal.setMes(rs.getInt("mes"));
                sal.setAno(rs.getInt("ano"));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                            ex.printStackTrace();
            }
        }

        return sal;
    }
    
    public void insert(Salario sal) {
        String query = "INSERT INTO salario (idDespesa, cpf, mes, ano)"
                + " VALUES(?, ?, ?, ?)";

        try {
            pst = Conexao.getConexao().prepareStatement(query);

            pst.setInt(1, sal.getIdDespesa());
            pst.setInt(2, sal.getCpf());
            pst.setInt(3, sal.getMes());
            pst.setInt(4, sal.getAno());

            pst.executeUpdate();

        } catch (Exception ex) {
                                        ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                            ex.printStackTrace();
            }
        }
    }

   public void save(Salario sal) {
        String query = "SELECT COUNT(*) FROM salario WHERE cpf = ? AND mes = ? AND ano = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, sal.getCpf());
            pst.setInt(2, sal.getMes());
            pst.setInt(3, sal.getAno());
            
            rs = pst.executeQuery();
            rs.next();

            if (rs.getInt("count") != 0) {
                insert(sal);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }

   public void delete(Salario sal) {
        String query = "DELETE FROM salario WHERE cpf = ? AND mes = ? AND ano = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, sal.getCpf());
            pst.setInt(2, sal.getMes());
            pst.setInt(3, sal.getAno());

            pst.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }

   public ArrayList<Salario> loadAll() {
        ArrayList<Salario> salarios = new ArrayList<Salario>();

        String query = "SELECT * FROM salario";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            rs = pst.executeQuery();

            while (rs.next()) {
                Salario sal = new Salario();

                sal.setIdDespesa(rs.getInt("idDespesa"));
                sal.setCpf(rs.getInt("cpf"));
                sal.setMes(rs.getInt("mes"));
                sal.setAno(rs.getInt("ano"));

                salarios.add(sal);
            }
        } catch (SQLException ex) {
            ex.getLocalizedMessage();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }

        return salarios;
    }
}
